package model;

import bean.Answer;
import bean.Poll;
import bean.PollUser;
import bean.Question;
import java.sql.*;
import java.util.ArrayList;

import connection.DbConnection;

/**
 * Note class is abstract so do not instantiate it.  Use methods by "AnswerImpl.setAnswer()"
 * @author s4099950
 *
 */
public abstract class AnswerImpl {
	
	/**
	 * Store response to question in database Answer table
	 * @param question is question object
	 * @param deviceId is string device id
	 * @param poll is Poll object
	 * @param response is String response value
	 */
	public static void setAnswer (Question question, String deviceId, String response) {
		// First check answer table for number of responses made by user - do after individual component
		// Second check question table for number of responses allowable	- do after individual component
		// Third.  If number of responses made < number of responses allowable ? Insert else update;	- do after individual component
		String user = getUserFromDevice(deviceId);
		PollUser pu = new PollUser();
		pu.setUsername(user);
		PollUserImpl.setUserAttributes(pu);
		String userDeviceId = user;
		if (pu.getGenericUser() == 1) {
			userDeviceId = deviceId;
		}
		System.out.println("username found for device is: "+user);
		ArrayList<Answer> answers = getUserAnswers(question, user);
		
		if (checkForOptions(question)) { 
			System.out.println("=== question is multichoice");
			// Multiple-choice questions
			if(!(question.getNumberOfAnswer() < answers.size())){
				insertAnswerOptionQuestion(question, deviceId, response);
			} else {
				// make updateAnswerOptionQuestion
				System.out.println("********** caught in setAnswer method of AnswerImpl");
			}
		} else {
			System.out.println("=== question is NOT multi choice");
			// No multiple-choice
			if(!(question.getNumberOfAnswer() < answers.size())){
				insertAnswer(question, deviceId, response);
			} else {
				updateAnswer(question, deviceId, response);
			}
		}
		

	}
	
	private static String getUserFromDevice(String deviceID){
		DbConnection conn = new DbConnection();
		try{
			System.out.println("device id: "+deviceID);
			String sql = "SELECT PollUsername FROM User_Device WHERE deviceID='"+deviceID+"'";
			PreparedStatement ps = conn.getConn().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(rs.next()){
				return rs.getString("PollUsername");
			}
		}catch(SQLException e){
			System.out.println("Error in getUserFromDevice");
		}
		return "";
	}
	
	/**
	 * 
	 * @return array of user answers if user has responses for this question
	 */
	private static ArrayList<Answer> getUserAnswers(Question question, String user){
		ArrayList<Answer> answers = new ArrayList<Answer>();
		try {
			DbConnection conn = new DbConnection();
			String sql = "SELECT * FROM ANSWER WHERE UsernameorDeviceID='"+user+"' AND QuestionID="+question.getQuestionID();
			PreparedStatement ps = conn.getConn().prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			
			while(rs.next()){
				Answer a = new Answer();
				a.setAnswerId(rs.getInt("AnswerID"));
				a.setAnswerNumber(rs.getInt("AnswerNumber"));
				a.setPollid(rs.getInt("PollID"));
				a.setQuestionID(rs.getInt("QuestionID"));
				a.setSubmitTime(rs.getDate("SubmitTime"));
				a.setText(rs.getString("Text"));
				a.setVersion(rs.getInt("Version"));
				answers.add(a);
			}
		} catch (NullPointerException npe) {
			npe.printStackTrace();
			System.out.println("******** Null Pointer Exception when executing getUserAnswers method.");
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			System.out.println("******** SQL problem when executing getUserAnswers method.");
			System.out.println("******** Ie. Your sql query is wrong!");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return answers;
	}
	
	/**
	 * Inserts the answer into the database
	 * */
	private static void insertAnswer(Question question, String user, String response){
		try {
			DbConnection conn = new DbConnection();
			
			String sql = "INSERT INTO ANSWER(QuestionId,PollId,PollVersion,UsernameorDeviceID,Text) " +
					"VALUES ("+question.getQuestionID()+"," +//questionID
					"(SELECT pollID FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+")," //pollID
					+"(SELECT PollVersion FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+"),'"//poll version
					+user+"','"//pollUsername
					+response+"')";//Text
			System.out.println(sql);
			PreparedStatement ps = conn.getConn().prepareStatement(sql);
			ps.executeQuery();
		} catch (NullPointerException npe) {
			npe.printStackTrace();
			System.out.println("******** Null Pointer Exception when executing insertAnswer method.");
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			System.out.println("******** SQL problem when executing insertAnswer method.");
			System.out.println("******** Ie. Your sql query is wrong!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	public static void updateAnswer (Question question, String user, String response) {
		try {
			DbConnection conn = new DbConnection();
			
			String sql = "UPDATE ANSWER SET Text = '"+response+"' WHERE SubmitTime = "+
					"(SELECT MIN (SUBMITTIME), ANSWERID FROM ANSWER WHERE UsernameorDeviceID ='"+user+"'"+
						" AND QuestionId = "+question.getQuestionID()+" " +
						" AND PollId = (SELECT pollID FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+";)"+
						" AND PollVersion = (SELECT PollVersion FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+";);).SubmitTime" +
				" AND UsernameorDeviceID = '"+user+"'"; 
			System.out.println(sql);
			PreparedStatement ps = conn.getConn().prepareStatement(sql);
			ps.executeQuery();
		} catch (NullPointerException npe) {
			npe.printStackTrace();
			System.out.println("******** Null Pointer Exception when executing insertAnswer method.");
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			System.out.println("******** SQL problem when executing insertAnswer method.");
			System.out.println("******** Ie. Your sql query is wrong!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static boolean checkForOptions (Question question) {
		try {
			DbConnection conn = new DbConnection();
			
			String sql = "SELECT COUNT(*) FROM OPTIONS WHERE QUESTIONID = "+question.getQuestionID();
			ResultSet rs = conn.doSelect(sql);
			rs.next();
			if (rs.getInt(1) > 0) {
				return true;
			} 			
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			System.out.println("****************** SQL problem when executing checkForOptions method of AnswerImpl.java");
		}
		return false;
	}
	
	private static void insertAnswerOptionQuestion (Question question, String user, String response){
		try {
			DbConnection conn = new DbConnection();
			
			// Insert empty answer into ANSWER TABLE
			String sql = "INSERT INTO ANSWER(QuestionId,PollId,PollVersion,UsernameorDeviceID) VALUES ("
							+question.getQuestionID()+"," 
							+"(SELECT pollID FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+"),"
							+"(SELECT PollVersion FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+"),'"
							+user+"')";
			System.out.println("sql 1 in insertAnswerOptionQuestion: "+sql);
			conn.doInsert(sql);
			
			// Select answer id from ANSWER table where just added
			sql = "SELECT ANSWERID FROM ANSWER WHERE QUESTIONID = "+question.getQuestionID()+
						" AND POLLID = (SELECT pollID FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+")"+
						" AND POLLVERSION = (SELECT PollVersion FROM Poll_Question WHERE QuestionID="+question.getQuestionID()+")"+
						" AND SUBMITTIME = (SELECT MAX(SUBMITTIME) FROM ANSWER)";
			System.out.println("sql 2 in insertAnswerOptionQuestion: "+sql);
			ResultSet rs = conn.doSelect(sql);
			rs.next();
					
			// Insert into ANSWER_OPTION table with answer id & option id
			/* Note this sql statement is assuming the option id is sequentially started from 1 AND the response is valid */
			sql = "INSERT INTO Answer_Option VALUES ("+rs.getString("AnswerId")+","+response+","+question.getQuestionID()+")";
			System.out.println("sql 2 in insertAnswerOptionQuestion: "+sql);
			conn.doInsert(sql);
		} catch (NullPointerException npe) {
			npe.printStackTrace();
			System.out.println("******** Null Pointer Exception when executing insertAnswer method.");
		} catch (SQLException sqle) {
			sqle.printStackTrace();
			System.out.println("******** SQL problem when executing insertAnswer method.");
			System.out.println("******** Ie. Your sql query is wrong!");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
}

